/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package staplers;

import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map.Entry;
import java.util.Set;
import org.hibernate.Query;
import org.hibernate.Session;

/**
 *
 * @author robert
 */


public class DBHelper {
    Session session = null;
    int retVal;
    
    public DBHelper() {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
    }
    
    public User login(String login, String pass)
    {     
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
        User user = null;
        try
        {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery ("from User as u where u.login=:login and u.password=:pass");
            q.setParameter("login", login);
            q.setParameter("pass", pass);
            user = (User)q.uniqueResult();
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        
        return user;
    }

    int register(User user) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            retVal = (Integer)session.save(user);
            tx.commit();
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;;
        } finally {
            
            return retVal;
        }
        
        
    }

    int updateUser(User user) {
        
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();    
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            session.update(user);
            tx.commit();
            retVal = 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
        
    }

    int deleteUser(User user) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            session.delete(user);
            tx.commit();
            retVal= 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal= -1;
        }
        finally {
            
            return retVal;
        }
    }

    List getCategoryList() {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Category> catList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Category as c order by c.name");
            catList = (List<Category>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        
        return catList;
    }
    
    Category getCategoryById(int id)
    {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        Category category = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Category as c where c.id=:catId");
            q.setParameter("catId", id);
            category = (Category)q.uniqueResult();

        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }        
        return category;
    }

    int defineNewCategory(Category category) {
        
         if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();   
        
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            retVal = (Integer)session.save(category);
            tx.commit();
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
        
        
    }

    public int defineNewAttributes(Set<Attribute> attributes) {
        
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();    
        Iterator<Attribute> iter = attributes.iterator();
        org.hibernate.Transaction tx = session.beginTransaction();
        while (iter.hasNext())
        {
            try {
                
                retVal = (Integer)session.save(iter.next());
                
            }
            catch (Exception e) {
                e.printStackTrace();
                session.close(); session = HibernateUtil.getSessionFactory().openSession();
                
                return -1;
            }
        }        
        tx.commit();
        
        
        return retVal;
    }

    int defineNewItem(Item item) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            retVal = (Integer)session.save(item);
            tx.commit();
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
        }
        
        return retVal;
    }

    int defineNewAttributeValues(Set<AttributeValue> attributeValues) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        Iterator<AttributeValue> iter = attributeValues.iterator();
        org.hibernate.Transaction tx = session.beginTransaction();
        while (iter.hasNext())
        {
            try {
                
                retVal = (Integer)session.save(iter.next());
                
            }
            catch (Exception e) {
                e.printStackTrace();
                session.close(); session = HibernateUtil.getSessionFactory().openSession();
                
                return -1;
            }
        }
        tx.commit();
        
        
        
        return retVal;
    }
    
    public List getAttributeList()
    {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Attribute> attList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Attribute");
            attList = (List<Attribute>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        
        return attList;
    }

    public List search(SearchQuery sq) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Item> itemList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            String query = "select distinct i from Item as i";
            if (sq.getAttributeValues() != null)
                query += ", AttributeValue as a where a.item.id=i.id and ";
            else
                query += " where ";
            boolean something = false;
            if (sq.name.length() > 0) {
                query+= "i.name like '%"+sq.name+"%' and ";
                something = true;
            }
            if (sq.manufacturer.length()>0) {
                query+= "i.manufacturer like '%"+sq.manufacturer+"%' and ";
                something = true;
            }
            if (sq.stock.length()>0) {
                query+= "i.stock>='"+sq.stock+"' and ";
                something = true;
            }
            if (sq.getAttributeValues() == null && something == false)
                return null;
            if (sq.getAttributeValues() != null)
            {
                Set<Entry<String,String>> attributeSet = sq.getAttributeValues().entrySet();
                Iterator<Entry<String,String>> iter = attributeSet.iterator();
                while (iter.hasNext())
                {
                    Entry<String,String> entry = iter.next();
                    query+= "a.attribute.id='"+entry.getKey()+"' and a.value='"+entry.getValue()+"' and ";
                }
                
            }
            
            if (query.endsWith("and "))
                query = query.substring(0, query.length()-4);
            query+=" order by i.name";
            Query q = session.createQuery(query);
            itemList = (List<Item>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        if (itemList.isEmpty())
            return null;
        return itemList;
    }

    int deleteItem(Item item) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            session.delete(item);
            tx.commit();
            retVal = 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
    }

    int updateItem(Item item) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            session.update(item);
            tx.commit();
            retVal = 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
    }

    int updateAttributeValues(Set<AttributeValue> attributeValues) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        Iterator<AttributeValue> iter = attributeValues.iterator();
        org.hibernate.Transaction tx = session.beginTransaction();
        while (iter.hasNext())
        {
            try {                
                session.update(iter.next()); 
                retVal = 0;
            }
            catch (Exception e) {
                e.printStackTrace();
                session.close(); session = HibernateUtil.getSessionFactory().openSession();
                
                return -1;
            }
        }
        tx.commit();
        
        
        return retVal;
    }

    List getUserList() {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<User> userList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from User as u order by u.login");
            userList = (List<User>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        
        return userList;
    }

    List searchUsers(String user2Search) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<User> userList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from User as u where u.login like :login order by u.login");
            q.setParameter("login", "%"+user2Search+"%");
            userList = (List<User>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        if (userList.isEmpty())
            return null;
        return userList;
    }

    int finalizeOrder(Order order) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            retVal = (Integer)session.save(order);
            tx.commit();
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
    }

    int finalizeOrderLinks(Set<OrderLink> orderLinks) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
        if (session.isOpen())
        {
            session.close();
            session = HibernateUtil.getSessionFactory().openSession();
        }
            
        Iterator<OrderLink> iter = orderLinks.iterator();
        org.hibernate.Transaction tx = session.beginTransaction();
        while (iter.hasNext())
        {
            try {
                
                retVal = (Integer)session.save(iter.next());
                
            }
            catch (Exception e) {
                e.printStackTrace();
                session.close();
                
                return -1;
            }
        }
        tx.commit();
        return retVal;
    }

    int deductStockAfterOrder(Set<OrderLink> orderLinks) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        Iterator<OrderLink> iter = orderLinks.iterator();
        org.hibernate.Transaction tx = session.beginTransaction();
        while (iter.hasNext())
        {
            try {
                OrderLink ol = iter.next();
                Query q = session.createSQLQuery("update Item as i set i.stock=i.stock-"+ol.getQuantity()+" where i.id='"+ol.getItem().getId()+"'");
                q.executeUpdate();
                
            }
            catch (Exception e) {
                e.printStackTrace();
                session.close(); session = HibernateUtil.getSessionFactory().openSession();
                
                return -1;
            }
        }
        tx.commit();
        
        
        return 0;
    }

    List getAllItems() {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Item> userList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Item as i order by i.name");
            userList = (List<Item>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        
        return userList;
    }

    List getItemsByCategory(Category categoryToSearch) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Item> userList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Item as i where i.category.id=:catId order by i.name");
            q.setParameter("catId", categoryToSearch.getId());
            userList = (List<Item>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } 
        
        return userList;
    }

    List getPriorOrdersByUser(User user) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Order> orderList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Order as o where o.user.id=:userId order by o.date desc");
            q.setParameter("userId", user.getId());
            orderList = (List<Order>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        if (orderList.isEmpty())
            return null;
        return orderList;
    }

    int deleteCategory(Category category) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            session.delete(category);
            tx.commit();
            retVal = 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
    }

    int deleteAttribute(Attribute attribute) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            session.delete(attribute);
            tx.commit();
            retVal = 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
    }

    int updateCategory(Category category) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            session.update(category);
            tx.commit();
            retVal = 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
    }

    List<Category> getCategoryByCategoryOf(Category categoryOf) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Category> catList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q;
            if (categoryOf == null)
                q = session.createQuery("from Category as c where c.categoryByCategoryOf IS NULL order by c.name");
            else
            {
                q = session.createQuery("from Category as c where c.categoryByCategoryOf.id=:catId order by c.name");
                q.setParameter("catId", categoryOf.getId());
            }
            catList = (List<Category>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } 
        
        return catList;
    }

    List getAttributesForCategory(int categoryId) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Attribute> attList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q;
            q = session.createQuery("from Attribute as a where a.category.id=:catId order by a.name");
            q.setParameter("catId", categoryId);
            attList = (List<Attribute>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } 
        
        return attList;
    }

    int deleteAttributesByItemId(Integer id) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createSQLQuery("delete from attributeValue where itemId="+id);
            q.executeUpdate();
            tx.commit();
            retVal = 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
            retVal = -1;
        } finally {
            
            return retVal;
        }
    }
    
     List getAllOrders() {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
            
        List<Order> orderList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Order as i order by i.date");
            orderList = (List<Order>)q.list();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        
        return orderList;
    }

    Item getStockById(Integer id) {
        if (!session.isOpen()) 
            session = HibernateUtil.getSessionFactory().openSession();
        if (session.isOpen())
        {
            session.close();
            session = HibernateUtil.getSessionFactory().openSession();
        }
            
       Item item = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Item as i where i.id=:itemId");
            q.setParameter("itemId", id);
            item = (Item)q.uniqueResult();
            
        } catch (Exception e) {
            e.printStackTrace();
            session.close(); session = HibernateUtil.getSessionFactory().openSession();
        } finally {
            
        }
        if (item != null)
            return item;
        else
            return null;
    }

    
    
    
}
